# -*- coding: utf-8 -*-
# This code written by ChanGOGOGO
"""
通话异常分析数据
"""
import pymysql
from util import (
    calc_percent,
    get_chinese_date,
    write_excel,
    logging,
    excel_save_dir,
    db_ip,
    db_port,
    db_username,
    db_password,
)


class ExportCallErrorAnalysis:
    def __init__(self, ui_data):
        self.data_date = ui_data["data_date"]
        self.business = ui_data.get("business")
        self.data_type = ui_data.get("data_type")
        self.start_time = ui_data.get("start_time")
        self.end_time = ui_data.get("end_time")
        self.task_ids = ui_data.get("target_datas")
        company_id = ui_data.get("company_id")
        self.db_name = f"yicall_{company_id}"
        self.table_index = ui_data.get("business_id")
        self.conn = pymysql.connect(
            host=db_ip,
            port=db_port,
            user=db_username,
            passwd=db_password,
            charset="utf8",
        )
        self.cursor = self.conn.cursor()

    def close_db(self):
        """关闭数据库连接"""
        self.cursor.close()
        self.conn.close()

    def query_task_ids(self):
        """如果客户端输入了任务ID，就查询输入的任务ID；如果没有输入任务ID，就查询时间范围内有进行拨打的任务ID"""
        if len(self.task_ids) >= 1:
            task_ids = self.task_ids
        else:
            sql = f"""select task_id from {self.db_name}.b_{self.table_index}_callout_session_ach_{self.data_date} 
            where callout_finish_time >='{self.start_time}' and callout_finish_time <= '{self.end_time}' 
            GROUP BY task_id;"""
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            task_ids = list()
            if len(result) >= 1:
                for x in result:
                    task_ids.append(x[0])
        return task_ids

    def query_scene_task(self):
        """查询场景下的任务信息"""
        scene_task_info = dict()
        task_ids = self.query_task_ids()
        task_id_len = len(task_ids)
        if task_id_len == 0:
            return scene_task_info
        elif task_id_len == 1:
            sql_condition = f"WHERE task_table.task_id='{task_ids[0]}'"
        else:
            sql_condition = f"WHERE task_table.task_id in {tuple(task_ids)}"
        sql = f"""SELECT scene_table.scene_name,task_table.task_id,task_table.task_name 
        FROM {self.db_name}.b_{self.table_index}_call_scene AS scene_table 
        LEFT JOIN {self.db_name}.b_{self.table_index}_callout_task AS task_table 
        ON scene_table.scene_id=task_table.scene_id {sql_condition};"""
        # logging.info(f'开始查询{self.data_type}的场景任务信息：\n{sql}')
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        if result:
            for scene_name, task_id, task_name in result:
                find_scene = scene_task_info.get(scene_name, -1)
                task_info = {"task_id": task_id, "task_name": task_name}
                if find_scene != -1:
                    scene_task_info[scene_name].append(task_info)
                else:
                    scene_task_info[scene_name] = [task_info]
        return scene_task_info

    def query_call_data(self):
        scene_task_info = self.query_scene_task()
        scene_call_data = dict()
        temp_excel_title = list()
        if scene_task_info:
            for scene_name, task_infos in scene_task_info.items():
                for task_info in task_infos:
                    task_id = task_info.get("task_id")
                    task_name = task_info.get("task_name")
                    task_call_data = dict()
                    sql = f"""select callout_dial_number,callout_connect_status,system_error,error_desc,count(*) 
                    from (SELECT task_id,callout_dial_number,callout_connect_status,system_error,
                    SUBSTRING_INDEX(system_error_desc, ':', -1) as error_desc 
                    FROM {self.db_name}.b_{self.table_index}_callout_session_ach_{self.data_date} 
                    WHERE callout_finish_time >= '{self.start_time}' AND callout_finish_time <= '{self.end_time}' 
                    and task_id={task_id}) temp where callout_connect_status NOT IN ( 80, 81, 82, 83, 84, 97, 98 ) 
                    group by callout_dial_number,callout_connect_status,error_desc;"""
                    logging.info(
                        f"开始查询{self.business}在{self.start_time}和{self.end_time}之间的拨打数据：\n{sql}"
                    )
                    self.cursor.execute(sql)
                    result = self.cursor.fetchall()
                    if len(result) >= 1:
                        for (
                            callout_dial_number,
                            callout_connect_status,
                            system_error,
                            error_desc,
                            count,
                        ) in result:
                            find_callout_dial_number = task_call_data.get(
                                callout_dial_number, -1
                            )
                            if find_callout_dial_number == -1:
                                if callout_connect_status == 100:
                                    task_call_data[callout_dial_number] = {
                                        "已接通": count
                                    }  # 已接通数量
                                else:
                                    if system_error == 0:
                                        task_call_data[callout_dial_number] = {
                                            "未接通挂断": count
                                        }  # 未接通挂断
                                    else:
                                        temp_excel_title.append(error_desc)
                                        task_call_data[callout_dial_number] = {
                                            error_desc: count
                                        }  # 通话异常
                            else:
                                if callout_connect_status == 100:
                                    # 如果按照system_error进行分组，接通状态100可能会分出几种，正常的以及接通后asr异常等等
                                    find_connected = task_call_data.get(
                                        callout_dial_number
                                    ).get("已接通", -1)
                                    if find_connected != -1:
                                        task_call_data[callout_dial_number][
                                            "已接通"
                                        ] += count  # 已接通数量
                                    else:
                                        task_call_data[callout_dial_number][
                                            "已接通"
                                        ] = count  # 已接通数量
                                else:
                                    if system_error == 0:
                                        task_call_data[callout_dial_number][
                                            "未接通挂断"
                                        ] = count  # 未接通挂断
                                    else:
                                        temp_excel_title.append(error_desc)
                                        task_call_data[callout_dial_number][
                                            error_desc
                                        ] = count  # 通话异常
                    # logging.info(f'任务[{task_name}]的拨打信息：\n{data2beautify(task_call_data)}')
                    find_scene = scene_call_data.get(scene_name, -1)
                    if find_scene == -1:
                        scene_call_data[scene_name] = {task_name: task_call_data}
                    else:
                        scene_call_data[scene_name][task_name] = task_call_data
            # logging.info(data2beautify(scene_call_data))
        temp_excel_title = list(set(temp_excel_title))
        temp_excel_title.sort()
        excel_title = list()
        for title in temp_excel_title:
            excel_title.append(title)
            excel_title.append("占比(%)")
        excel_title[:0] = [
            "场景名称",
            "任务名称",
            "主叫号码",
            "拨打量",
            "已接通",
            "接通率",
            "未接通挂断",
            "占比(%)",
            "异常总数",
            "占比(%)",
        ]
        return scene_call_data, excel_title

    @staticmethod
    def handle_excel_datas(excel_datas: list):
        """处理初步的excel数据，主要是计算各种占比"""
        new_excel_datas = list()
        for excel_data in excel_datas:
            new_excel_data = list()
            call_num = excel_data[3]  # 拨打量
            connected_num = excel_data[4]  # 接通量
            connected_rate = calc_percent(connected_num, call_num, 4)
            new_excel_data[:5] = excel_data[:5]
            new_excel_data.append(connected_rate)
            not_connected_num = excel_data[6]
            new_excel_data.append(not_connected_num)
            not_connected_rate = calc_percent(not_connected_num, call_num)
            new_excel_data.append(not_connected_rate)
            error_total_num = call_num - connected_num - not_connected_num  # 异常总数
            new_excel_data.insert(8, error_total_num)
            error_total_rate = calc_percent(error_total_num, call_num, 4)
            new_excel_data.insert(9, error_total_rate)
            for error_num in excel_data[10:-1:2]:
                error_rate = calc_percent(error_num, error_total_num, 4)
                new_excel_data.extend([error_num, error_rate])
            new_excel_datas.append(new_excel_data)
        return new_excel_datas

    def export(self):
        result = self.query_call_data()
        if result:
            scene_call_data, excel_title = result
            if scene_call_data:
                excel_datas = list()
                for scene_name, task_call_data in scene_call_data.items():
                    for task_name, line_call_data in task_call_data.items():
                        for line_name, line_call_info in line_call_data.items():
                            excel_data = [scene_name, task_name, line_name]
                            call_num = 0  # 拨打量
                            temp_excel_data = list()
                            for title in excel_title[4:]:
                                num = line_call_info.get(title, 0)
                                if isinstance(num, int):
                                    call_num += num
                                temp_excel_data.append(num)
                            excel_data.append(call_num)
                            excel_data.extend(temp_excel_data)
                            excel_datas.append(excel_data)
                finally_excel_datas = self.handle_excel_datas(excel_datas)
                sheet_infos = [["通话异常分析", excel_title, finally_excel_datas]]
                start_date = get_chinese_date(self.start_time)
                end_date = get_chinese_date(self.end_time)
                if start_date == end_date:
                    file_name = f"{self.business}_{self.data_type}({start_date})"
                else:
                    file_name = (
                        f"{self.business}_{self.data_type}({start_date}-{end_date})"
                    )
                excel_name = write_excel(sheet_infos, excel_save_dir, file_name, True)
                return {"code": 0, "excel_names": [excel_name], "msg": None}
            else:
                msg = (
                    f"⚠️ <font color='red'>**{self.business}**</font>的<font color='green'>{self.data_type}</font>导出失败。"
                    f"原因：\n<font color='blue'>在{self.start_time}至{self.end_time}没有查询到{self.data_type}数据！</font>"
                )
                return {"code": 1, "excel_names": [], "msg": msg}
        else:
            msg = (
                f"⚠️ <font color='red'>**{self.business}**</font>的<font color='green'>{self.data_type}</font>导出失败。"
                f"原因：\n<font color='blue'>在{self.start_time}至{self.end_time}没有查询到{self.data_type}数据！</font>"
            )
            return {"code": 1, "excel_names": [], "msg": msg}


if __name__ == "__main__":
    ...
